import string

import xlwt
import json
import pymysql



class  excelwr():
   # 当前月份
   sqlByIndex1 = """select  *   from   temp_ods_ytb_view_data_indicator2_da where  templetname  = '%s'  and  timecode = '%s'"""

   sqlBytempletname = """select  indicatorname   from   temp_ods_ytb_view_data_indicator2_da where   1=1  """
   areasql = "select   distinct areaName   from  temp_ods_ytb_view_data_indicator2_da"
   calcsql = "select  calcName   from   temp_ods_ytb_view_data_indicator2_da where   1=1  "
   member1sql = "select  member1Name   from   temp_ods_ytb_view_data_indicator2_da where   1=1  "

   def maindef(self,json,jsonpath,xlspath):
       db = pymysql.connect(host='192.168.0.103', port=3306, user='root', passwd='12345678', db='szpt', charset='utf8')
       newtime = '202104'
       # 获取地区词典
       area = []
       try:
           cursor = db.cursor()
           # 执行SQL语句
           cursor.execute(self.areasql)
           # 获取所有记录列表
           results = cursor.fetchall()
           resnum = 0
           if (len(results) > 0):
               for row in results:
                   try:
                       if row[0] != None:
                           area.append(row[0])
                   except Exception as err:
                       1 + 1
           # print(rowNum,colNum,resnum)
       except Exception as e:
           1 + 1
       finally:
           cursor.close()

       f = open(jsonpath, encoding="utf-8")
       data = f.read()
       f.close()
       path = xlspath
       json = json.loads(data)
       #excelwr = excelwr()
       # 生成模板
       res = self.analysis_json_todata(path, json)
       # 获取参数坐标
       index_list = res[0]
       # print(index_list)
       dimension_list = res[1]
       ws = res[2]
       wb = res[3]
       # print(index_list)
       # print(dimension_list)
       tab = json['tab']
       indexType = tab['indexType']
       target = tab['target']
       field = tab['field']
       # 是否展示单位
       unitShow = tab['unitShow']
       index_len = len(index_list)
       dimension_len = len(dimension_list)
       # print (indexType,index_len,dimension_len)
       for i in range(index_len):
           # 行
           rowNum = 0
           # 列
           colNum = 0
           dataindex = index_list[i]

           for k in range(dimension_len):
               datadimension = dimension_list[k]
               # 取坐标
               if indexType == 1:
                   colNum = datadimension[1]
                   rowNum = dataindex[2]
               if indexType == 2:
                   colNum = dataindex[1]
                   rowNum = datadimension[2]
               # print(colNum,rowNum)
               index1 = dataindex[0]
               index2 = dataindex[3]
               dimension1 = datadimension[0]
               dimension2 = datadimension[3]
               res = self.getIndexName(db, index1, index2, dimension1, dimension2, area)


               #获取字段是否合计的配置
               issum = 1
               if dataindex[4]==2 or datadimension[4]==2:
                   issum=2

               # 字段没有查找到
               if res[0] == '' and res[2] == '':
                  continue


               sql = self.sqlByIndex1 % (field,newtime)
               sql = sql  + self.sqljoint(res)

               if sql == '' :
                   continue

               try:
                   cursor = db.cursor()
                   print(sql)
                   cursor.execute(sql)
                   # 获取所有记录列表
                   results = cursor.fetchall()
                   resnum = 0
                   resval ="%s(%s)"


                   rowdata = None

                   #当出现多条及配置是否合计，判断是否取唯一
                   if issum == 1  and  len(results) > 1 :
                       rowdata=self.resgetone(results)
                       if rowdata != None :
                           resnum = resnum + rowdata[18]
                           unit = rowdata[17]
                           calcUnitName = rowdata[13]
                   if (len(results) > 0) and rowdata == None:
                       for row in results:
                           # print(row)
                           try:
                               if row[18] != None:
                                   #判断当前字段是否需要合计  issum = 1 不需要合并
                                   resnum = resnum + row[18]
                                   unit = row[17]
                                   calcUnitName = row[13]
                                   if issum ==1 :

                                       break
                           except Exception as err:
                               1 + 1

                   #单位
                   if unitShow != None and  unitShow == 1:
                       if res[3] !='' and calcUnitName != None:
                          resval = resval % (resnum, calcUnitName)
                       elif res[0] !='' and unit != None:
                          resval = resval % (resnum, unit)
                       else:
                          resval = resnum
                   else:
                       resval = resnum

                   #print(rowNum,colNum,resval)
                   ws.write(colNum, rowNum, resval)

               except Exception as e:
                   print("Finished with error: ", e)
                   1 + 1
               finally:
                   cursor.close()
       wb.save(path)



   #多结果尝试获取唯一
   def resgetone(self, results):
       rowdata=None
       for row in results:
           if row[4] == '-1' :
               return  row
       return rowdata



   #sql拼接
   # indexName, areaName, calcName , member1Name
   def  sqljoint(self,res):
       sql = ''
       if res[0] != '' :
           sql +=  " and  indicatorName = '" + res[0] + "' "
       if res[1] != '' :
           sql +=  " and  locate('" + res[1] + "', areaName) > 0  "
       if res[2] != '' :
           sql +=  " and  calcName = '" + res[2] + "' "
       if res[3] != '' :
           sql +=  " and  member1Name = '" + res[3] + "' "
       return  sql



   def  write_excel(self,path):
        wb = xlwt.Workbook(encoding='utf-8')
        ws = wb.add_sheet('test')
        ws.write_merge(0, 0, 0, 3, '这是一个标题')
        wb.save(path)

   def  analysis_json(self,json,targetNum,parentTarget):
        global fieldNum
        global result_list
        global levelNum
        target  = json['target'] if ('target' in json) else None
        field = json['field'] if ('field' in json) else None
        sum = json['sum'] if ('sum' in json) else None
        fieldNum=0
        num =self.analysis_json_num(json)
        if targetNum > levelNum:
            levelNum = targetNum
        member = [ field, target, targetNum, 'targetlist' in json,parentTarget, num ,sum ]
        if target != None and  target != '' :
            result_list.append(member)
        #print(field,target, targetNum, 'targetlist' in json,parentTarget,num)
        if ('targetlist' in json):
          #有上一层时则+1
          if ('target' in json):
             targetNum=targetNum+1
          targetlist = json['targetlist']
          for key in range(len(targetlist)) :
              listdata = targetlist[key]
              if ('targetlist' not in listdata):
                 fieldNum = fieldNum+1

              #父级元素叠加
              if field!=None  and str.find(parentTarget, field) == -1:
                  if parentTarget != '':
                     parentTarget = parentTarget, '@', field
                  else:
                     parentTarget = field
                  parentTarget=''.join(parentTarget)

              self.analysis_json(targetlist[key], targetNum,parentTarget)
        return  fieldNum

   def analysis_json_num(self,json):
       global fieldNum
       global maxFieldNum
       if ('targetlist' in json):
           targetlist = json['targetlist']
           for key in range(len(targetlist)):
               listdata = targetlist[key]
               if ('targetlist' not in listdata):
                   fieldNum = fieldNum + 1
                   if fieldNum>maxFieldNum:
                       maxFieldNum = fieldNum
               self.analysis_json_num(targetlist[key])
       return fieldNum



   def  analysis_json_todata(self,path,json):
       index_list = []
       dimension_list = []
       parentTarget_list=[]
       global fieldNum
       global maxFieldNum
       global result_list
       global levelNum
       fieldNum = 0
       result_list = []
       levelNum = 0
       maxFieldNum=0
       font = xlwt.Font()
       # 字体类型
       font.name = 'name Times New Roman'
       # 字体大小，11为字号，20为衡量单位
       font.height = 20 * 11
       # 字体加粗
       font.bold = True
       style = xlwt.XFStyle()
       al = xlwt.Alignment()
       al.horz = 0x02  # 设置水平居中
       al.vert = 0x01  # 设置垂直居中
       style.alignment = al
       style.font = font
       wb = xlwt.Workbook(encoding='utf-8')
       ws = wb.add_sheet('首页')
       tall_style1 = xlwt.easyxf('font:height 720')  # 设置行高
       tall_style2 = xlwt.easyxf('font:height 480')  # 设置行高
       width = 256 * 20
       targetNum = 1
       tab = json['tab']
       #表格类型
       indexType = tab['indexType']
       remarks = tab['remarks']


       #indexType =1  时  横坐标为指标  纵坐标为维度 indexType =2  时  横坐标为维度 纵坐标为指标

       jsondata = json['index'] if (indexType == 1 ) else json['dimension']
       self.analysis_json(jsondata, targetNum, '')
       #主表名称
       remarkscol = maxFieldNum
       member = [tab['field'], tab['target'], 0, 'targetlist' in json, None, maxFieldNum,None]
       result_list.append(member)

       #print(result_list)

       for i in range(levelNum + 1):
           rownumst = 1
           colnum = 0
           for k in range(len(result_list)):
               num = result_list[k][2]
               if num == i:
                   colnum = result_list[k][5]
                   if colnum > 0:
                       colnum = colnum - 1
                   if i == 0:
                       ws.write_merge(i, i, 0,  colnum+1, result_list[k][1], style)
                       ws.col(0).width = width
                   else:
                       #print(i, i, rownumst, colnum, result_list[k][1])
                       ws.write_merge(i, i, rownumst, rownumst + colnum, result_list[k][1], style)
                       ws.col(rownumst).width = width


                       #将数据加入指标列表，以组装sql
                       if  result_list[k][3] == False :
                           rowdata = [result_list[k][0], i, rownumst, result_list[k][4],result_list[k][6]]
                           #print(rowdata)
                           if (indexType == 1):
                                   index_list.append(rowdata)
                                   #print(result_list[k][1],i,'行',rownumst,'列',':父级:',result_list[k][4])
                           else:
                                   dimension_list.append(rowdata)

                   first_row = ws.row(i)  # 获取sheet页的第一行
                   first_row.set_style(tall_style1)  # 给第一行设置tall_style样式，也就是行高
                   rownumst = rownumst + colnum + 1

       '''
       for i in range(len(result_list)):
           t = result_list[i][3]
           if t== False:
               print('横轴字段', result_list[i][0])
       '''

       # 指标名称栏写入
       ws.write_merge(1, levelNum, 0, 0, '指标名称', style)


       rownum = levelNum + 1
       fieldNum = 0
       result_list = []
       levelNum = 0
       targetNum = 0
       width = 256 * 30
       style2 = xlwt.XFStyle()
       style2.font = font
       al = xlwt.Alignment()
       al.horz = 0x01  # 设置水平居中
       al.vert = 0x01  # 设置垂直居中
       style2.alignment = al
       jsondata = json['dimension'] if (indexType == 1 ) else json['index']
       self.analysis_json(jsondata, targetNum, '')
       #print(result_list)
       for i in range(len(result_list)):
           num = result_list[i][2]
           '''
           if num !=0 :
              print('纵轴字段', result_list[i][0])
           '''
           val = result_list[i][1]
           for j in range(num):
               #print(num)
               val = '      ' + val
           ws.write_merge(i + rownum, i + rownum, 0, 0, val, style2)
           #if result_list[i][3] == False:
           rowdata = [result_list[i][0], i + rownum, 0, result_list[i][4], result_list[i][6]]
           if (indexType != 1):
                   index_list.append(rowdata)
                   #print(rowdata)
           else:
                   dimension_list.append(rowdata)
           ws.col(0).width = width
           first_row = ws.row(i + rownum)  # 获取sheet页的第一行
           first_row.set_style(tall_style2)  # 给第一行设置tall_style样式，也就是行高

       #备注栏添加
       #备注栏行数

       remarksrow = rownum+len(result_list)
       print('remarkscol',remarkscol)
       ws.write_merge(remarksrow, remarksrow, 0, remarkscol, remarks)

       return index_list,dimension_list,ws,wb

   def getIndexName(self,db, index1, index2, dimension1, dimension2, area):
       # 指标名称
       indexName = ''
       # 区域
       areaName = ''
       # 字段
       fieldName = ''
       calcName = ''
       member1Name=''
       try:
           cursor = db.cursor()
           data_list = []
           data_list.append(index1)
           data_list.append(dimension1)
           # 所有指标字段整合
           if index2 != '' and index2 != None:
               indexlist = index2.split('@')
               for k in range(len(indexlist)):
                   data_list.append(indexlist[k])
           if dimension2 != '' and dimension2 != None:
               indexlist = dimension2.split('@')
               for k in range(len(indexlist)):
                   data_list.append(indexlist[k])
           # 指标分类
           print(data_list)
           calcsql1 = '( '
           sqlBytempletname1 = '( '
           member1sql1 = '( '
           for k in range(len(data_list)):
               if data_list[k] in area:
                   areaName = data_list[k]
                   continue
               if k < len(data_list) - 1:
                   calcsql1 = calcsql1 + "calcName  = '" + data_list[k] + "'  or  "
                   sqlBytempletname1 = sqlBytempletname1 + "indicatorname  = '" + data_list[k] + "'  or  "
                   member1sql1 = member1sql1+ "member1Name  = '" + data_list[k] + "'  or  "
               else:
                   calcsql1 = calcsql1 + "calcName  = '" + data_list[k] + "' "
                   sqlBytempletname1 = sqlBytempletname1 + "indicatorname  = '" + data_list[k] + "'   "
                   member1sql1 = member1sql1 + "member1Name  = '" + data_list[k] + "'   "

           calcsql1 = calcsql1 + ' )'
           sqlBytempletname1 = sqlBytempletname1 + ')'
           member1sql1 = member1sql1+ ' )'

           #获取calcName
           sql = self.calcsql + ' and ' + calcsql1
           # 执行SQL语句
           cursor.execute(sql)
           # 获取所有记录列表
           results = cursor.fetchall()

           if (len(results) > 0):
               for row in results:
                   try:
                       if row[0] != None:
                           calcName = row[0]
                   except Exception as err:
                       1 + 1

           # 获取indicatorname
           sql = self.sqlBytempletname + ' and ' + sqlBytempletname1
           # 执行SQL语句
           cursor.execute(sql)
           # 获取所有记录列表
           results = cursor.fetchall()
           if (len(results) > 0):
               for row in results:
                   try:
                       if row[0] != None:
                           indexName = row[0]
                   except Exception as err:
                       1 + 1

           # 获取member1Name
           sql = self.member1sql + ' and ' + member1sql1
           # 执行SQL语句
           cursor.execute(sql)
           # 获取所有记录列表
           results = cursor.fetchall()
           if (len(results) > 0):
               for row in results:
                   try:
                       if row[0] != None:
                           member1Name = row[0]
                   except Exception as err:
                       1 + 1


       except Exception as e:
           1 + 1
       cursor.close()

       # 无地区，默认全省
       if areaName == '':
           areaName = '浙江'

       return indexName, areaName, calcName , member1Name


if __name__ == '__main__':
    excelwr = excelwr()
    excelwr.maindef(json,"json_file3.txt","test.xls")